package com.ssi.report.service.impl;

/** 
 * 
 * 	@author	: Kroekpong Sakulchai
 *  Date 			: 09-MAY-2014
 *  Version 		: Alpha
 *  Team			: Software Solution And Integration
 *   
 * **/

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.ssi.report.common.constant.ReportConstant;
import com.ssi.report.common.jdbc.JDBCConnection;
import com.ssi.report.common.utils.SSDateUtil;
import com.ssi.report.common.utils.SSLogUtil;
import com.ssi.report.model.ReportModel;
import com.ssi.report.service.ReportService;

public class ReportServiceImpl extends JDBCConnection implements ReportService{

	public static SSLogUtil logger = new SSLogUtil();
	
	private static final StringBuilder SQL_QUERY = new StringBuilder()
	.append(" SELECT T1.* , CC.UNIT, CC.CAL_PERIOD, CC.BUSINEE_DAY, 					                              ")
	.append(" CC.FLOAT_PRICE, CC.COMD_REF_PRICE_1, CC.COMD_REF_PRICE_2			                                      ")
	.append(" , CC.SPECIFY_PRICE						                                                              ")
	.append(" FROM (						                                                                          ")
	.append("       SELECT  T.DESCRIPTION COMPANY_NAME						                                          ")
	.append("             , T.ADRESSE1 ATTENTION						                                              ")
	.append("             , T.ADRESSE2						                                                          ")
	.append("             , T.ADRESSE3						                                                          ")
	.append("             , T.ADRESSE4						                                                          ")
	.append("             , T.ADRESSE5						                                                          ")
	.append("             , T.FAX						                                                              ")
	.append("             , T.TELEPHONE						                                                          ")
//	.append("             , O.DATE_OPERATION DEAL_DATE						                                          ")
	.append("             , TO_CHAR(O.DATE_OPERATION,'DD/MM/YYYY')  DEAL_DATE					 					  ") // Return Date format DD/MM/YYYY // TODO
	.append("             , O.REF_EXTERNE_2 KPLUS_NO						                                          ")
	.append("             , O.Num_Operation KTP_NO						                                              ")
	.append("             , O.TITRE INSTUMENT						                                                  ")
	.append("             , T1.DESCRIPTION INSTU_DESC						                                          ")
	.append("             , (SELECT A.DESCRIPTION						                                              ")
	.append("                FROM RTDBA.AFFILIE_CODE A						                                          ")
	.append("                WHERE A.NOM_TABLE = 'op.tiers'						                                      ")
	.append("                AND   A.ATTRIBUT = 'ISDA_FLAG'						                                      ")
	.append("                AND   O.PORTEFEUILLE_TIERS = A.CODE_REF) FLAG_ISDA						                  ") 
//	.append("               , 'Y' FLAG_ISDA																                  ") // TODO TEST "FLAG_ISDA" 
	.append("             , (SELECT A.DESCRIPTION						                                              ")
	.append("                FROM RTDBA.AFFILIE_CODE A						                                          ")
	.append("                WHERE A.NOM_TABLE = 'op.tiers'						                                      ")
	.append("                AND   A.ATTRIBUT = 'ISDA_DATE'						                                      ")
	.append("                AND   O.PORTEFEUILLE_TIERS = A.CODE_REF) DATE_ISDA 						              ")
	.append("             , (SELECT A.DESCRIPTION						                                              ")
	.append("                FROM RTDBA.AFFILIE_CODE A						                                          ")
	.append("                WHERE A.NOM_TABLE = 'op.tiers'						                                      ")
	.append("                AND   A.ATTRIBUT = 'CSA_FLAG'						                                      ")
	.append("                AND   O.PORTEFEUILLE_TIERS = A.CODE_REF) FLAG_CSA  						              ")
//	.append("               , 'N' FLAG_CSA																                  ") // TODO TEST "FLAG_CSA" 
	.append("             ,  (SELECT A.DESCRIPTION						                                              ")
	.append("                FROM RTDBA.AFFILIE_CODE A						                                          ")
	.append("                WHERE A.NOM_TABLE = 'op.titre'						                                      ")
	.append("                AND   A.ATTRIBUT = 'PAY_BUSN'						                                      ")
	.append("                AND   O.TITRE = A.CODE_REF) BUSN_DAY                              						  ")
	.append("             , CASE WHEN ( LOWER(O.COMMENTAIRE) LIKE '%average%' ) THEN						          ")
	.append("                  SUBSTR(O.COMMENTAIRE,						                                          ")
	.append("                         INSTR(O.COMMENTAIRE,'from')+5,						                          ")
	.append("                         INSTR(SUBSTR(O.COMMENTAIRE,INSTR(O.COMMENTAIRE,'from')+5),',')-1				  ")	
	.append("                        ) 						                                                          ")
	.append("               ELSE 						                                                              ")
	.append("                  TO_CHAR(O.DATE_ECHEANCE,'DD/MM/YYYY')              						              ")
	.append("               END TERMINATE_DATE						                                                  ")
	.append("             , CASE WHEN ( LOWER(O.COMMENTAIRE) LIKE '%average%' ) THEN						          ")
	.append("                    'A'						                                                          ")
	.append("               ELSE						                                                              ")
	.append("                    'M'						                                                          ")
	.append("               END MULT_AVG_TYPE						                                                  ")
	.append("             , O.NOMINAL_1 QTY      						                                              ")
	.append("             , CASE WHEN (O.OP_REFERENCE IS NOT NULL) THEN 						                      ")
	.append("                     (SELECT P1.SIGNE						                                              ")
	.append("                      FROM RTDBA.HISTO_OPERATION_REP O1 						                          ")
	.append("                          ,RTDBA.OPERATION_POSTE P1						                              ")
	.append("                      WHERE O.OP_REFERENCE = O1.NUM_OPERATION						                      ")
	.append("                      AND   O1.POSTE = P1.CODE)						                                  ")
	.append("               ELSE P.SIGNE END BS_FLAG						                                          ")
	.append("             , O.DEVISE_2 CCY_FIX 						                                                  ")
	.append("             , O.COURS_TERME RATE_FIX						                                              ")
	.append("             , (SELECT A.DESCRIPTION						                                              ")
	.append("                FROM RTDBA.AFFILIE_CODE A						                                          ")
	.append("                WHERE A.NOM_TABLE = 'op.tiers'						                                      ")
	.append("                AND   A.ATTRIBUT = 'PAY_CURR'						                                      ")
	.append("                AND   O.PORTEFEUILLE_TIERS = A.CODE_REF) PAY_CCY						                  ")
//	.append("               , 'USD' PAY_CCY																                  ") // TODO TEST "PAY_CCY" 'THB'
	.append("             , (SELECT CB.DESCRIPTION || ',' || CB.CORRESPONDANT_1 || ' Account no. ' || CB.CODE_BBAN	  ")				
	.append("                FROM RTDBA.COMPTE_BANQUE CB						                                      ")
	.append("                WHERE T.CODE = CB.ENTITE						                                          ")
	.append("                AND   CB.DEFAUT = 'N' AND CB.FLAG_ACTIF = 'N'						                      ")
	.append("                AND   CB.CODE like 'COMD_%'						                                      ")
	.append("                   ) BANK_OF_CUST						                                                  ")
	.append("       FROM  RTDBA.HISTO_OPERATION_REP O 						                                          ")
	.append("           , RTDBA.TIERS T						                                                          ")
	.append("           , RTDBA.TITRE T1						                                                      ")
	.append("           , RTDBA.OPERATION_POSTE P						                                              ")
	.append("       WHERE O.PORTEFEUILLE_TIERS = T.CODE						                                          ")
	.append("       AND   O.TITRE = T1.CODE						                                                      ")
	.append("       AND   O.POSTE = P.CODE						                                                      ")
	.append("       AND SUBSTR(O.TITRE,0,3) IN ('BRT','CTA','DHA','GAS','JET','LLA',                                  ")
	.append(" 'LPA','LSU','MAA','NSA','SBO','SMA','WHT','XAU')						                                  ")
	.append("       AND O.REF_EXTERNE_2 IS NOT NULL              						                              ");
//	.append("       --AND TO_CHAR(O.Date_Saisie,'YYYYMMDD') = TO_CHAR(SYSDATE - 1,'YYYYMMDD')						  ")
//	.append("       --AND TO_CHAR(O.Date_Saisie,'YYYYMMDD') = '20140416'						                      ")
//	.append("       ) T1						                                                                      ")
//	.append(" , RTDBA.COMD_CONFIRM CC						                                                          ")
//	.append(" WHERE SUBSTR(T1.INSTUMENT,0,3) = CC.COMD_TYPE						                                      ")
//	.append(" AND T1.MULT_AVG_TYPE  = CC.MULT_AVG_TYPE																  ");
	
	@Override
	public List<ReportModel> getReportDataList(Date date) {
		
		/*--------------------------  Where condition ------------*/
		SQL_QUERY.append("     AND TO_CHAR(O.Date_Saisie,'YYYYMMDD') = '").append(SSDateUtil.dateToString(date)).append("'")
		.append("      ) T1						                                                                      ")
		.append(" , RTDBA.COMD_CONFIRM CC						                                                          ")
		.append(" WHERE SUBSTR(T1.INSTUMENT,0,3) = CC.COMD_TYPE						                                      ")
		.append(" AND T1.MULT_AVG_TYPE  = CC.MULT_AVG_TYPE																  ");

		ResultSet rs = queryBySql(SQL_QUERY.toString());
		List<ReportModel> reportModels = new ArrayList<ReportModel>();
		int count = 0;
		
		try {
			
		    /*---------- Rows Data -----------*/
			while (rs.next()) {
				
				count++;
				
				int limit = ReportConstant.CONFIG_APP_LIMIT_ROWS;
				if(!"TRUE".equals(ReportConstant.CONFIG_APP_LIMIT)){
					count = 0;
				}
				
				if(count <= limit){ // >>> Limit Row For Test  FIXME
					
					String avgtype = rs.getString("MULT_AVG_TYPE");
					String t_date = rs.getString("TERMINATE_DATE");
					String effdate = "";
					String termdate = "";
					
					if (StringUtils.isNotEmpty(t_date)) {
						if ("M".equals(avgtype)) {
							effdate = t_date;
							termdate = t_date;
						} else {
							String sp[] = t_date.split("-");
							if (sp.length > 0) {
								effdate = sp[0];
								termdate = sp[1];
							}
						}
					}
					
					ReportModel model = new ReportModel();
					model.setCompanyName(rs.getString("COMPANY_NAME"));
					model.setAttention(StringUtils.isEmpty(rs.getString("ATTENTION"))? "":rs.getString("ATTENTION"));
					model.setAdresse2(StringUtils.isEmpty(rs.getString("ADRESSE2"))? "":rs.getString("ADRESSE2"));
					model.setAdresse3(StringUtils.isEmpty(rs.getString("ADRESSE3"))? "":rs.getString("ADRESSE3"));
					model.setAdresse4(StringUtils.isEmpty(rs.getString("ADRESSE4"))? "":rs.getString("ADRESSE4"));
					model.setAdresse5(StringUtils.isEmpty(rs.getString("ADRESSE5"))? "":rs.getString("ADRESSE5"));
					model.setFax(StringUtils.isEmpty(rs.getString("FAX"))? "":rs.getString("FAX"));
					model.setTelephone(StringUtils.isEmpty(rs.getString("TELEPHONE"))? "":rs.getString("TELEPHONE"));
					model.setDealDate(StringUtils.isEmpty(rs.getString("DEAL_DATE"))? "":rs.getString("DEAL_DATE"));
					model.setKplusNo(rs.getString("KPLUS_NO"));
					model.setKtpNo(rs.getString("KTP_NO"));
					model.setInstument(rs.getString("INSTUMENT"));
					model.setInstuDesc(rs.getString("INSTU_DESC"));
					model.setFlagIsda(rs.getString("FLAG_ISDA"));
					model.setEffectiveDate(effdate);
					model.setTerminateDate(termdate);
					model.setMultAvgType(rs.getString("MULT_AVG_TYPE"));
					model.setQty(StringUtils.isEmpty(rs.getString("QTY"))? "": ReportConstant.QTY_FORMAT.format(rs.getFloat("QTY")));
					model.setBsFlag(rs.getString("BS_FLAG"));
					model.setCcyFix(StringUtils.isEmpty(rs.getString("CCY_FIX"))? "": rs.getString("CCY_FIX"));
					model.setRateFix(StringUtils.isEmpty(rs.getString("RATE_FIX"))? "":  ReportConstant.PRICE_FORMAT.format(rs.getFloat("RATE_FIX")));
					model.setPayCcy(StringUtils.isEmpty(rs.getString("PAY_CCY"))? "":rs.getString("PAY_CCY"));
					model.setUnit(StringUtils.isEmpty(rs.getString("UNIT"))? "": rs.getString("UNIT"));
					model.setCalPeriod(rs.getString("CAL_PERIOD"));
					model.setBusineeDay(rs.getString("BUSINEE_DAY"));
					model.setFloatPrice(rs.getString("FLOAT_PRICE"));
					model.setComdRefPrice1(rs.getString("COMD_REF_PRICE_1"));
					model.setComdRefPrice2(rs.getString("COMD_REF_PRICE_2"));
					model.setSpecifyPrice(rs.getString("SPECIFY_PRICE"));
					model.setFlagCsa(rs.getString("FLAG_CSA"));
					model.setBankOfCust(StringUtils.isEmpty(rs.getString("BANK_OF_CUST"))? "": rs.getString("BANK_OF_CUST"));
					model.setDateIsda(StringUtils.isEmpty(rs.getString("DATE_ISDA"))? "": rs.getString("DATE_ISDA"));
					model.setBusnDay(StringUtils.isEmpty(rs.getString("BUSN_DAY"))? "": rs.getString("BUSN_DAY"));
					
					/*-------- Fixed Price Payer & Floating Price Payer -------*/
					if(StringUtils.isNotEmpty(rs.getString("BS_FLAG")) && 
							ReportConstant.CONFIG_FLAG_1.equals(rs.getString("BS_FLAG"))){
						model.setFixedPricePayer(ReportConstant.CONFIG_REPORT_PARTY_A);
						model.setFloatingPricePayer(ReportConstant.CONFIG_REPORT_PARTY_B);
					}else{
						model.setFixedPricePayer(ReportConstant.CONFIG_REPORT_PARTY_B);
						model.setFloatingPricePayer(ReportConstant.CONFIG_REPORT_PARTY_A);
					}
					
					/*------ FLAG_CSA :: Valuation Time :::: ---------*/
					if(StringUtils.isNotEmpty(rs.getString("FLAG_CSA")) && 
							ReportConstant.CONFIG_FLAG_Y.equals(rs.getString("FLAG_CSA"))){
						model.setValuationLabel(ReportConstant.CONFIG_REPORT_VALUATION_LABEL);
						model.setValuation(ReportConstant.CONFIG_REPORT_VALUATION_TXT);
					}else{
						model.setValuationLabel("");
						model.setValuation("");
					}
					
					/*------ PAY_CCY :: Reference Rate :::: ---------*/
					if(StringUtils.isNotEmpty(rs.getString("PAY_CCY")) && 
							ReportConstant.CONFIG_THB.equals(rs.getString("PAY_CCY"))){
						model.setSettlementAmt(ReportConstant.CONFIG_REPORT_SETTLE_AMOUNT);
						model.setReferenctRateLabel(ReportConstant.CONFIG_REPORT_REFERENCE_LABEL);
						model.setReferenctRate(ReportConstant.CONFIG_REPORT_REFERENCE_TXT);
						model.setPaymentParty_A(ReportConstant.CONFIG_REPORT_PAYMENT_PARTY_A1);
					}else{
						model.setSettlementAmt("");
						model.setReferenctRateLabel("");
						model.setReferenctRate("");
						model.setPaymentParty_A(ReportConstant.CONFIG_REPORT_PAYMENT_PARTY_A2);
					}
					
					reportModels.add(model);
				}
				
			} // Comment This When Test // TODO
			
		} catch (SQLException e) {
			logger.debug("SQLException: " + e.getMessage());
		}catch (Exception e) {
			e.printStackTrace();
		}
		
		return reportModels;
		
	}
	
}
